﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace QuanLiSinhVien_v01.DAO
{
    class DataProvider
    {
        public string ConectionString { get; set; }
        private SqlDataAdapter _adapter;
        private SqlCommand _command;
        private SqlConnection _connection;

        public DataProvider()
        {
            ConectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSQLLocalHost"].ConnectionString;
        //    ConectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSQLFile"].ConnectionString;
            //ConectionString = @"Data Source=(local)\SQLEXpress;Initial Catalog=QuanLiSinhVien_NhomQuyKy;Integrated Security=True;";
        }

        public void Connect()
        {
            try
            {
                _connection = new SqlConnection(ConectionString);
                _connection.Open();
            }
            catch (SqlException ex)
            {
                throw new Exception(String.Format("Kết nối CSDL thất bại: {0}",ex.Message));
            }
        }

        public void Disconect()
        {
            _connection.Close();
        }

        public void ExcuteNonQuery(string strConmand)
        {
            try
            {
                Connect();
                _command = new SqlCommand(strConmand, _connection);
                _command.ExecuteNonQuery();
                Disconect();                  
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable ExcuteQuery(string strCommand)
        {
            DataSet ds = new DataSet();
            try
            {
                Connect();
                _adapter = new SqlDataAdapter(strCommand,_connection);
                _adapter.Fill(ds);
                Disconect();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }

        public ArrayList ConvertDatatableToArrayListObject(DataTable dt)
        {
            ArrayList result = new ArrayList();
            for (int i = 0; i < dt.Rows.Count; i++ )
            {
                Object obj = GetDataFromDataRow(dt, i);
                result.Add(obj);
            }
            return result;
        }

        virtual protected Object GetDataFromDataRow(DataTable dt, int rowIndex)
        {
            return null;
        }

    }
}
